package br.com.revenda.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.revenda.conexao.conectionFactory;
import br.com.revenda.modelo.Cliente;

public class ClienteDAO {

	Connection con;
	
	public ClienteDAO(){
	con = new conectionFactory().getConnection();	
	}
	
	
	public void salvar(Cliente c){
		String sql = "insert into cliente (nome , cpf , fone , celular , cep , endereco , cidade , estado)values(?,?,?,?,?,?,?,?)";
		try {
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, c.getNome());
			pstm.setString(2, c.getCpf());
			pstm.setString(3, c.getFone());
			pstm.setString(4, c.getCelular());
			pstm.setString(5, c.getCep());
			pstm.setString(6, c.getEndereco());
			pstm.setString(7, c.getCidade());
			pstm.setString(8, c.getEstado());
			pstm.execute();
			pstm.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
		
		public void deletar(Cliente c){
			String sql = "delete from cliente where idCliente=?";
			try {
				PreparedStatement pstm = con.prepareStatement(sql);
				pstm.setInt(1, c.getIdCliente());
				pstm.execute();
				pstm.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		
		public void alterar(Cliente c){
			String sql = "update cliente set nome=? , cpf=? , fone=? , celular=? , cep=? , endereco=? , cidade=? , estado=? where idCliente=?";
			try {
				PreparedStatement pstm = con.prepareStatement(sql);
				pstm.setString(1, c.getNome());
				pstm.setString(2, c.getCpf());
				pstm.setString(3, c.getFone());
				pstm.setString(4, c.getCelular());
				pstm.setString(5, c.getCep());
				pstm.setString(6, c.getEndereco());
				pstm.setString(7, c.getCidade());
				pstm.setString(8, c.getEstado());
				pstm.setInt(9, c.getIdCliente());
				pstm.execute();
				pstm.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		
		public List<Cliente> listaTodos(){
			List<Cliente> lista = new ArrayList<Cliente>();
			String sql = "select * from cliente";
			try {
				PreparedStatement pstm = con.prepareStatement(sql);
				ResultSet rs = pstm.executeQuery();
				
				while(rs.next()){
					Cliente c = new Cliente();
					c.setNome(rs.getString("nome"));
					c.setCpf(rs.getString("cpf"));
					c.setFone(rs.getString("fone"));
					c.setCelular(rs.getString("celular"));
					c.setCep(rs.getString("cep"));
					c.setEndereco(rs.getString("endereco"));
					c.setCidade(rs.getString("cidade"));
					c.setEstado(rs.getString("estado"));
					c.setIdCliente(rs.getInt("idCliente"));
					lista.add(c);
				}
					rs.close();
					pstm.close();
					con.close();
				} catch (SQLException e) {
				e.printStackTrace();
			}
			return lista;
		}
		
		
		public List<Cliente> listaPoOpcao(String opcao , String cli){
			List<Cliente> lista = new ArrayList<Cliente>();
			String sql = "select * from cliente where "+ opcao+"  LIKE ?";
			try {
				PreparedStatement pstm = con.prepareStatement(sql);
				pstm.setString(1, cli+"%");
				ResultSet rs = pstm.executeQuery();
				
				while(rs.next()){
					Cliente c = new Cliente();
					c.setNome(rs.getString("nome"));
					c.setCpf(rs.getString("cpf"));
					c.setFone(rs.getString("fone"));
					c.setCelular(rs.getString("celular"));
					c.setCep(rs.getString("cep"));
					c.setEndereco(rs.getString("endereco"));
					c.setCidade(rs.getString("cidade"));
					c.setEstado(rs.getString("estado"));
					c.setIdCliente(rs.getInt("idCliente"));
					lista.add(c);
				}
					rs.close();
					pstm.close();
					con.close();
				} catch (SQLException e) {
				e.printStackTrace();
			}
			return lista;
		}
		
		
		
		
		
		
		public Integer BuscaPorId(String nome){
			String sql = "select * from cliente where nome=?";
			Integer numero = 0;
			try {
				PreparedStatement pstm = con.prepareStatement(sql);
				pstm.setString(1, nome);
				ResultSet rs = pstm.executeQuery();
				numero = rs.getInt("idCliente");					
				rs.close();
				pstm.close();
				con.close();
			}catch (SQLException e) {
				e.printStackTrace();
			}
			return numero;
		}
		
		
		
		
	
	}
	
	
	
	
	

